package com.zwh.spring.demo.service.impl;

import java.sql.PreparedStatement;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.cache.annotation.CacheConfig;
import org.springframework.cache.annotation.CacheEvict;
import org.springframework.cache.annotation.CachePut;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.mysql.jdbc.Statement;
import com.zwh.spring.demo.domain.User;
import com.zwh.spring.demo.mapper.primary.UserMapper;
import com.zwh.spring.demo.service.UserService;

@CacheConfig(cacheNames="users")
@Service
public class UserServiceImpl implements UserService {
	private Logger logger = LoggerFactory.getLogger(getClass());
	
	@Autowired
	@Qualifier("primaryJdbcTemplate")
	private JdbcTemplate jdbcTemplate;
	
	@Autowired
	private UserMapper userMapper;

	@Override
	public Long create(String name, String password, Integer age) {
		
		KeyHolder keyHolder = new GeneratedKeyHolder();
		
		jdbcTemplate.update((conn) -> {
			PreparedStatement ps = conn.prepareStatement("insert into user(name, password, age) values(?,?,?)", Statement.RETURN_GENERATED_KEYS);
			ps.setString(1, name);
			ps.setString(2, password);
			ps.setInt(3, age);;
			return ps;
		}, keyHolder);
		
		return keyHolder.getKey().longValue();
		
		//jdbcTemplate.update("insert into user(name, password, age) values(?,?,?)", name, password, age);
	}

	@Override
	public void deleteByName(String name) {
		jdbcTemplate.update("delete from user where name = ?", name);
	}

	@Override
	public Integer getAllUsers() {

		return jdbcTemplate.queryForObject("select count(1) from user", Integer.class);
	}

	@Override
	public void deleteAllUsers() {
		userMapper.deleteAll();
	}

	@Override
	@Transactional("primaryTransectionManager")
	public Integer add5Users() {
		
		userMapper.insertByParam("tom", "123456", 85);
		userMapper.insertByParam("jack", "123456", 115);
		userMapper.insertByParam("rose_exception", "123456", 122);
		userMapper.insertByParam("gelly", "123456", 77);
		userMapper.insertByParam("jackson", "123456", 34);
		
		return 5;
	}

	@Override
	public User addUser(User user) {
		Long id = create(user.getName(), user.getPassword(), user.getAge());
		user.setId(id);
		return user;
	}


	@Cacheable(key="#p0")
	@Override
	public User findById(Long id) {
		SimpleDateFormat sdf = new SimpleDateFormat("HH:mm:ss,SSS");
		logger.info("Service读取数据库:" + sdf.format(new Date()));
		return select(id);
	}

	private User select(Long id) {
		SqlRowSet rowSet = jdbcTemplate.queryForRowSet("Select * from user where id = ?", id);
		while(rowSet.next()) {
			String name = rowSet.getString("name");
			String password = rowSet.getString("password");
			int age = rowSet.getInt("age");
			return new User(name, password, age);
		}
		
		return null;
	}

	@CachePut(key="#p0")
	@Override
	public User changePasswordById(Long id, String password) {
		logger.info("修改数据库，。。。会更新缓存");
		jdbcTemplate.update("update user set password=? where id=?", password, id);
		
		return select(id);
	}

	@CacheEvict(key ="#p0",allEntries=true)
	@Override
	public void deleteById(Long id) {
		logger.info("修改数据库，。。。清除所有缓存");
		jdbcTemplate.update("delete from user where id = ?", id);
	}
	
	

}
